Skip Headers

Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 8 Administering the Gateway

Read this chapter to understand how to administer your gateway either by using the Oracle Gateway Monitor or by entering commands at the command line prompt. The following sections are included:

- Overview of the Oracle Gateway Monitor

- Starting the Oracle Gateway Monitor

- Starting and Stopping the Gateway

- Starting the TCP/IP Listener

- Displaying the Gateway Call Stack

- Working with Gateway Job Locks

- Displaying Open Files

- Gateway Security

- Streams Replication

## 8.1 Overview of the Oracle Gateway Monitor

The Oracle Gateway Monitor is a facility that is provided with the Oracle Transparent Gateway for DB2/400. This facility permits a single interface to perform the following functions:

- start and stop an Oracle gateway instance

- work with Oracle gateway jobs

- start the TCP/IP listener job

- display the gateway call stack

- work with the gateway job locks

- display the open files of the gateway

## 8.2 Starting the Oracle Gateway Monitor

To execute the Oracle gateway monitor, you must log on to the gateway instance user ID and have the gateway instance library included in your library list. To start the Oracle gateway monitor, enter the following command:

    ORAMON

The main menu for the Oracle Gateway Monitor is displayed as shown in Example 8-1, "Monitor Oracle Subsystems".

Example 8-1 Monitor Oracle Subsystems

    ___________________________________________________________________________________________________
                               Monitor Oracle Subsystems          System:  AS400A

     Position to _____________  Update frequency  *OFF

       Type Options, press Enter.
          2=Start   4=End   5=Work with   7=Listen   10=Display call stack
          11=Work with locks   14=Display open files

                                                      Job      Aux   Aux    CPU
       Opt      Name               Type      Status   Number   I/O    KB    Secs
       -        ORACLE             SBS       ACTV                0     0      .0
       -          LISTENER         LIS       SELW     015826   564  3386     2.8
       -          RUNORAGTTP        PJ       PSRW     015823   124  3725     1.6
       -          RUNORAGTTP        PJ       PSRW     015822   138  3737     1.5

     F1=Help   F2=Change list   F3=Exit   F5=Refresh   F8=Start auto update
    ___________________________________________________________________________________________________

The columns in the panel in Example 8-1, "Monitor Oracle Subsystems" represent the following:

Opt

  is used to perform various functions on subsystems and jobs. For example, option 2 starts a gateway subsystem. Enter an option number next to the subsystem or job name, and then press Enter. You can select options for more than one subsystem or job at a time. The option numbers are listed in the panel of Example 8-1.

Name

  displays the names of Oracle subsystems and jobs.

Type

  is the type of entry for this line:

  - `ASJ -` automatically started job

  - `LIS -` listener

  - `PJ -` pre started job waiting for start request

  - `SBS -` subsystem

  - `SRV -` server job

  - `UNK -` unknown

Status

  is the status of the subsystem or job that is associated with the line.

  For subsystems (type `SBS`):

  - `ACTV -` active subsystem

  - `ERROR -` error while determining subsystem status

  - `INACTV -` inactive subsystem

  - `NOAUTH -` user has no authorization to start or stop subsystem

  For jobs, refer to the Help information for the Status column of the WRKACTJOB panel.

Job Number

  is the OS/400 job number for job-related lines. It is blank for subsystem lines.

Aux I/O

  is the total number of auxiliary storage I/O operations that were performed.

Aux KB

  is the current amount of auxiliary storage occupied by the job, in kilobytes (units of 1024 bytes).

CPU Secs

  is the total CPU time used, in seconds.

To display specific gateway subsystems and jobs, you must enter the subsystem into the Oracle gateway monitor subsystem by pressing PF2. This displays a panel where you can enter the name of the Oracle gateway subsystems. The panel in Example 8-1, "Monitor Oracle Subsystems" shows a single gateway subsystem with a single listener and two prestarted gateway jobs.

To work with a specific gateway subsystem or job, you must enter an option number in the `Opt` field next to the subsystem or job that is displayed on the main panel.

In Example 8-1, "Monitor Oracle Subsystems", the `Update frequency` field is set to `*OFF`. This means that no updates of the information are displayed until you press PF5. If you want the screen to be updated periodically, then you need to press PF8. The default update refresh period is 30 seconds, but you can set it to a value between 30 seconds and 600 seconds.

If help is needed at any time, then pressing PF1 on any of the panels will display more information about a specific panel.

## 8.3 Starting and Stopping the Gateway

Entering a 2 in the Option field on the main panel allows you to start a gateway subsystem. `ACTV` is displayed in the `STATUS` field of a subsystem (SBS) when the gateway subsystem is started. To start the gateway subsystem, you must be authorized to use the `STRSBS` command.

To stop the gateway subsystem or a job in a gateway subsystem, move the cursor to the Opt field that is next to the subsystem or job that you want to shut down, and enter a `4`. This ends the specified gateway subsystem or job. When the gateway subsystem has shut down, `INACTV` is displayed in the field next to the subsystem or job that ended. To shut down the gateway, you must be authorized to use the `ENDSBS` command.

### 8.3.1 Working with Oracle Gateway Jobs

To work with gateway jobs, specify option 5 next to the job. This displays the OS/400 Work with Job menu to work with a specific gateway job.

### 8.3.2 Starting and Stopping the Gateway Using the Command Line

An instance of the Oracle Transparent Gateway for DB2/400 runs within an OS/400 subsystem. All jobs relating to that instance run within the subsystem. You use the `STRSBS` command to start a subsystem. For example, to start an instance of a gateway called `ORACLE`, you enter the following command:

    STRSBS ORACLE/ORACLE

where the first `ORACLE` is a library name, and the second `ORACLE` is the name of a subsystem.

To stop a gateway, you must end the subsystem by using the `ENDSBS` command. For example, to stop a gateway called `ORACLE`, to immediately end all jobs within the `ORACLE` subsystem, and to stop the subsystem itself, you enter the following command:

    ENDSBS ORACLE OPTION(*IMMED)

## 8.4 Starting the TCP/IP Listener

Specify option 7 in the Opt field that is next to the gateway subsystem name if you want to start the gateway listener. The gateway listener is identified by `LIS` in the `TYPE` field that is displayed on the main menu.

## 8.5 Displaying the Gateway Call Stack

Displaying the gateway call stack is not needed for most installations but might be requested by Oracle Support Services during problem isolation and determination. Entering option 10 in the Opt field displays a call stack for a specific job.

## 8.6 Working with Gateway Job Locks

Specify option 11 in the Opt field next to a specific job in order to display and work with object locks that are held by the gateway. This option displays the OS/400 Work with Job Locks panel.

## 8.7 Displaying Open Files

Specify option 14 in the Opt field next to a specific job to show the files that are open for that specific job. This option displays the OS/400 Display Open Files panel.

## 8.8 Gateway Security

The Oracle user ID and password are passed over the database link to the gateway to authorize gateway users to DB2/400 objects. If the `CONNECT TO` clause is specified when creating the database link, then the user ID and password that are sent to the gateway are those that are specified in this clause. If the `CONNECT TO` clause is omitted from the database link specification, then the Oracle user ID and password of the task that is using the database link are passed to the gateway for authorization.

Caution: You must keep the gateway recovery user ID and password synchronized with the OS/400 User Profile and password. The gateway recovery user ID and password must be valid to OS/400 at all times. If, when transaction recovery is being attempted by Oracle, the password for the recovery user ID is not valid or if the recovery user ID itself is invalid (it may have expired, for example), an ORA-1017 error (invalid user ID or password) will be inserted into the Oracle Database alert log. The `CHGRECOPRF` command is used to change the recovery profile parameters (including recovery user ID and password). For more information about using `CHGRECOPRF`, refer to "CHGRECOPRF, Change Recovery Profile Parameters".

## 8.9 Streams Replication

The Oracle Transparent Gateway for DB2/400 and Heterogeneous Services now support replication to DB2/400 by using Oracle Streams. Oracle Streams is a rule-based process that allows changes to an Oracle Database table to be captured and applied to an equivalent DB2/400 table, based on user-written rules.

Note: Replication from an Oracle Database to DB2/400 is supported. Replication from DB2/400 to an Oracle Database is not supported at this time.

Before setting up your Streams replication environment, ensure that archivelog is enabled.

Note: Streams will not work if archivelog is not enabled.

An example of a simple table replication follows:

First you should grant the necessary authorizations to your Streams admin user ID.

    CONNECT SYS/SYS_PASSWORD AS SYSDBA

    GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE
      TO strmadmin IDENTIFIED BY strmadminpw;

    GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
    GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
    GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
    GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;
    GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
    GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
      privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
      grantee => 'strmadmin',
      grant_option => FALSE);
    END;
    /

    BEGIN
      DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
      privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
      grantee => 'strmadmin',
      grant_option => FALSE);
    END;
    /

Then, set up the Streams queue and the database link that the apply process will use.

    CONNECT strmadmin/strmadminpw

    EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

    DROP DATABASE LINK strmdblink.your.domain.com;

    CREATE DATABASE LINK strmdblink.your.domain.com
      CONNECT TO userid IDENTIFIED BY password
         USING 'tnsnames_entry';

Next, create the capture and apply processes and define the replication rules.

    CONNECT SYS/SYS_PASSWORD AS SYSDBA

    ALTER SYSTEM ARCHIVE LOG CURRENT;

    CONNECT strmadmin/strmadminpw

    --- ---------------------------------------------------------------
    --- Stop the capture process if it's already active.
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_CAPTURE_ADM.STOP_CAPTURE(
        capture_name => 'db2400_capture');
    END;
    /

    --- ---------------------------------------------------------------
    --- Stop the apply process if it's already active.
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.STOP_APPLY(
        apply_name => 'apply_2_db2400');
    END;
    /

    --- ---------------------------------------------------------------
    --- Define the capture rule, this one captures changes to scott.emp
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
        schema_name => 'scott',
        streams_type => 'capture',
        streams_name => 'db2400_capture',
        queue_name => 'strmadmin.streams_queue',
        include_dml => true,
        include_ddl => true);
    END;
    /

    --- ---------------------------------------------------------------
    --- Set the capture instantiation level
    --- ---------------------------------------------------------------
    DECLARE
      iscn NUMBER; -- Variable to hold instantiation SCN value
    BEGIN
      iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

      DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
        source_object_name => 'scott.emp',
        source_database_name => 'ORAv92',
        instantiation_scn => iscn,
        apply_database_link => 'strmdblink.your.domain.com');
    END;
    /

    --- ---------------------------------------------------------------
    --- Drop the apply process if it already exists.
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.DROP_APPLY(
        apply_name => 'apply_2_db2400');
    END;
    /

    --- ---------------------------------------------------------------
    --- Create the apply process
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.CREATE_APPLY(
        queue_name => 'strmadmin.streams_queue',
        apply_name => 'apply_2_db2400',
        apply_database_link => 'strmdblink.your.domain.com',
        apply_captured => true);
    END;
    /

    --- ---------------------------------------------------------------
    --- Create the apply rule
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_STREAMS_ADM.ADD_TABLE_RULES(
        table_name => 'scott.emp',
        streams_type => 'apply',
        streams_name => 'apply_2_db2400',
        queue_name => 'strmadmin.streams_queue',
        include_dml => true,
        include_ddl => false,
        source_database => 'ORAv92');
    END;
    /

    --- ---------------------------------------------------------------
    --- Turn on tracing for the apply process (be careful, this
    --- generates alot of output).
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.SET_PARAMETER(
        apply_name => 'apply_2_db2400',
        parameter => 'trace_level',
        value => 127 );
    END;
    /

    --- ---------------------------------------------------------------
    --- Turn off disable_on_error for the apply process
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.SET_PARAMETER(
        apply_name => 'apply_2_db2400',
        parameter => 'disable_on_error',
        value => 'n');
    END;
    /

    --- ---------------------------------------------------------------
    --- Start the apply process.
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_APPLY_ADM.START_APPLY(
        apply_name => 'apply_2_db2400');
    END;
    /

    --- ---------------------------------------------------------------
    --- Start the capture process.
    --- ---------------------------------------------------------------
    BEGIN
      DBMS_CAPTURE_ADM.START_CAPTURE(
        capture_name => 'db2_capture');
    END;
    /

For detailed information about Oracle streams replication, refer to Oracle Streams Concepts and Administration.